from django.shortcuts import render, redirect, HttpResponse, HttpResponseRedirect
from django.http import JsonResponse
from django.urls import reverse  
from django.db.models import F, Q, Count, CharField, Case, When, Value, IntegerField, ExpressionWrapper, Func, Max
from django.db.models.functions import Replace, Cast, Concat
from django.db.models.query import QuerySet
from django.core import serializers  
from django.views.decorators.csrf import csrf_exempt
from .models import NoinfoWatchrecord, NoinfoUpdating, NoinfoProduct, NoinfoTheme
import re, json
from datetime import datetime, timedelta


# 首页
def index(request):
    return render(request, 'index.html')

# 查询作品
def search_product(request):
    return render(request, 'search_product.html') # 返回作品页面
def search_product_get(request):
    products = NoinfoProduct.objects.all() # 作品全部数据
    themes = products.values('theme').distinct().order_by('theme') # 系列唯一值
    products = list(products.order_by('-date_start').values()) # 格式化全部数据
    themes = list(themes)
    data = {
        'products': products,
        'themes': themes
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def search_product_post(request):
    if request.method == 'POST':
        name_val = request.POST.get('name_val')
        type_val = request.POST.get('type_val')
        if(type_val == '全部'):
            type_list = NoinfoProduct.objects.filter(name__icontains=name_val).order_by('-date_start').values()
        else:
            type_list = NoinfoProduct.objects.filter(name__icontains=name_val, type=type_val).order_by('-date_start').values()
        type_list = list(type_list)
        return JsonResponse(type_list, safe=False)

# 增加作品
def add_product(request):
    return render(request, "add_product.html")
def add_product_get(request):
    latest = NoinfoProduct.objects.latest('id')
    latest = serializers.serialize('json', [latest])
    data = {
        'latest': latest,
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def add_product_post(request):
    theme = request.POST.get("theme") 
    title = request.POST.get("title")  
    season = request.POST.get("season")
    subtitle = request.POST.get("subtitle")
    if season is None or season=="":
        if subtitle is None or subtitle=="":
            name = str(title)
        else:
            name = str(title) + ':' + str(subtitle)
    else:
        if subtitle is None or subtitle=="":
            name = str(title) + str(season)
        else:
            name = str(title) + str(season) + ':' + str(subtitle)
    playmode = request.POST.get("playmode")
    type = request.POST.get("type")
    dim = request.POST.get("dim")
    episodes = request.POST.get("episodes")
    if episodes == "":
        episodes = None
    duration = request.POST.get("duration")
    if duration == "":
        duration = None
    episode_start = request.POST.get("episode_start")
    if episode_start == "":
        episode_start = None
    episode_end = request.POST.get("episode_end")
    if episode_end == "":
        episode_end = None
    date_start = request.POST.get("date_start")
    if date_start == "":
        date_start = None
    date_end = request.POST.get("date_end")
    if date_end == "":
        date_end = None
    producer = request.POST.get("producer")
    director = request.POST.get("director")
    words = request.POST.get("words")
    if words == "":
        words = None
    chapters = request.POST.get("chapters")
    if chapters == "":
        chapters = None
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        NoinfoProduct.objects.create(
            theme = theme,
            name = name,
            title = title,
            season = season,
            subtitle = subtitle,
            playmode = playmode,
            type = type,
            dim = dim,
            episodes = episodes,
            duration = duration,
            episode_start = episode_start,
            episode_end = episode_end,
            date_start = date_start,
            date_end = date_end,
            producer = producer,
            director = director,
            words = words,
            chapters = chapters,
            create_time = create_time
        )
        code_product = 'success'
    except Exception as e:
        code_product = str(e)
    
    try:
        themes = NoinfoTheme.objects.filter(theme=theme)
        if themes:
            max_date = NoinfoProduct.objects.filter(theme=theme).aggregate(Max('date_start'))['date_start__max']
            if str(max_date) <= date_start:
                NoinfoTheme.objects.filter(theme=theme).update(
                    status_type = type,
                    status_view = name,
                    update_time = create_time
                ) 
                code_theme = 'update'
            code_theme = 'notupdate'
        else:
            NoinfoTheme.objects.create(
                theme = theme,
                status_view = name,
                source_name = theme,
                source_type = type,
                create_time = create_time
            )
            code_theme = 'create'
        
    except Exception as e:
        code_theme = str(e)
    status = {
        'code_product': code_product,
        'code_theme': code_theme
    }
    return JsonResponse(status, safe=False)
    
# 更新作品
def update_product(request):
    return render(request, "update_product.html")
def update_product_get(request):
    uid = request.GET.get("id")
    product = NoinfoProduct.objects.get(id=uid) # 根据id查找对应数据
    product = serializers.serialize('json', [product])
    data = {
        'product': product,
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def update_product_post(request):
    id = request.POST.get("id")
    theme = request.POST.get("theme") 
    title = request.POST.get("title")  
    season = request.POST.get("season")
    subtitle = request.POST.get("subtitle")
    if season is None or season=="":
        if subtitle is None or subtitle=="":
            name = str(title)
        else:
            name = str(title) + ':' + str(subtitle)
    else:
        if subtitle is None or subtitle=="":
            name = str(title) + str(season)
        else:
            name = str(title) + str(season) + ':' + str(subtitle)
    playmode = request.POST.get("playmode")
    type = request.POST.get("type")
    dim = request.POST.get("dim")
    episodes = request.POST.get("episodes")
    if episodes == "":
        episodes = None
    duration = request.POST.get("duration")
    if duration == "":
        duration = None
    episode_start = request.POST.get("episode_start")
    if episode_start == "":
        episode_start = None
    episode_end = request.POST.get("episode_end")
    if episode_end == "":
        episode_end = None
    date_start = request.POST.get("date_start")
    if date_start == "":
        date_start = None
    date_end = request.POST.get("date_end")
    if date_end == "":
        date_end = None
    producer = request.POST.get("producer")
    director = request.POST.get("director")
    words = request.POST.get("words")
    if words == "":
        words = None
    chapters = request.POST.get("chapters")
    if chapters == "":
        chapters = None
    update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        NoinfoProduct.objects.filter(id=id).update(
            name=name,
            theme = theme,
            title = title,
            season = season,
            subtitle = subtitle,
            playmode = playmode,
            type = type,
            dim = dim,
            episodes = episodes,
            duration = duration,
            episode_start = episode_start,
            episode_end = episode_end,
            date_start = date_start,
            date_end = date_end,
            producer = producer,
            director = director,
            words = words,
            chapters = chapters,
            update_time = update_time
        ) 
        code_product = 'success'
    except Exception as e:
        code_product = str(e)
    try:
        themes = NoinfoTheme.objects.filter(theme=theme)
        if themes:
            max_date = NoinfoProduct.objects.filter(theme=theme).aggregate(Max('date_start'))['date_start__max']
            if str(max_date) <= date_start:
                NoinfoTheme.objects.filter(theme=theme).update(
                    status_type = type,
                    status_view = name,
                    update_time = update_time
                ) 
                code_theme = 'update'
            code_theme = 'notupdate'
        else:
            NoinfoTheme.objects.create(
                theme = theme,
                status_view = name,
                source_name = theme,
                source_type = type,
                create_time = update_time
            )
            code_theme = 'create'
        
    except Exception as e:
        code_theme = str(e)
    status = {
        'code_product': code_product,
        'code_theme': code_theme
    }
    return JsonResponse(status, safe=False)

# 查询系列
def search_theme(request):
    return render(request, 'search_theme.html') # 返回作品页面
def search_theme_get(request):
    themes = NoinfoTheme.objects.all() # 系列全部数据
    # themes = products.values('theme').distinct().order_by('theme') # 系列唯一值
    # products = list(products.order_by('-date_start').values()) # 格式化全部数据
    themes = list(themes.values())
    data = {
        'themes': themes
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def search_theme_post(request):
    if request.method == 'POST':
        theme_val = request.POST.get('theme_val')
        type_val = request.POST.get('type_val')
        print(type_val)
        if type_val == 'all':
            theme_list = NoinfoTheme.objects.filter(theme__icontains=theme_val).values()
        elif type_val == 'universe':
            theme_list = NoinfoTheme.objects.filter(theme__icontains=theme_val, universe__isnull=False).exclude(Q(universe='')).values()
        elif type_val == 'noupdate':
            theme_list = NoinfoTheme.objects.filter(theme__icontains=theme_val, update_time__isnull=True).values()
        theme_list = list(theme_list)
        return JsonResponse(theme_list, safe=False)

# 更新系列
def update_theme(request):
    return render(request, "update_theme.html")
def update_theme_get(request):
    uid = request.GET.get("id")
    theme = NoinfoTheme.objects.get(id=uid) # 根据id查找对应数据
    theme = serializers.serialize('json', [theme])
    data = {
        'theme': theme,
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def update_theme_post(request):
    id = request.POST.get("id")
    theme = request.POST.get("theme") 
    status_view = request.POST.get("status_view")  
    source_name = request.POST.get("source_name")
    source_type = request.POST.get("source_type")
    universe = request.POST.get("universe")
    author = request.POST.get("author")
    status = request.POST.get("status")
    update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        NoinfoTheme.objects.filter(id=id).update(
            source_name = source_name,
            source_type = source_type,
            universe = universe,
            author = author,
            status = status,
            update_time = update_time
        ) 
        code = 'success'
    except Exception as e:
        code = str(e)
    status = {
        'code': code
    }
    return JsonResponse(status, safe=False)

# 增加记录
def add_watch(request):
    return render(request, "add_watch.html")
def add_watch_get(request):
    watch_names = NoinfoWatchrecord.objects.values('name')
    watch_names = [watch_name['name'] for watch_name in watch_names]
    notincludes = NoinfoProduct.objects.exclude(name__in=watch_names).order_by('name').values()
    notincludes = list(notincludes)
    data = {
        'notincludes': notincludes
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def add_watch_name_post(request):
    type = request.POST.get('type')
    if type is None:
        name_list = NoinfoWatchrecord.objects.all().order_by('name').values('name').distinct()
    else:
        name_list = NoinfoWatchrecord.objects.filter(type=type).order_by('name').values('name').distinct()
    name_list = list(name_list)
    data = {
        'name_list': name_list
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def add_watch_post(request):
    name = request.POST.get("name")
    code = NoinfoProduct.objects.get(name=name).id
    type = request.POST.get("type")
    times = request.POST.get("times") 
    date_watch_start = request.POST.get("date_watch_start")
    if date_watch_start == "":
        date_watch_start = None
    date_watch_end = request.POST.get("date_watch_end")
    if date_watch_end == "":
        date_watch_end = None
    status = request.POST.get("status")
    create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        NoinfoWatchrecord.objects.create(
            name=name,
            code=code,
            times=times,
            date_watch_start=date_watch_start,
            date_watch_end=date_watch_end,
            status=status,
            type=type,
            create_time=create_time
        )
        code = 'success'
    except Exception as e:
        code = str(e)
    status = {
        'code': code
    }
    return JsonResponse(status, safe=False)
    
# 更新记录
def update_watch(request):
    return render(request,"update_watch.html")
def update_watch_get(request):
    id = request.GET.get("id")
    watch = NoinfoWatchrecord.objects.get(id=id) # 根据id查找对应数据
    watch = serializers.serialize('json', [watch])
    data = {
        'watch': watch,
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def update_watch_post(request):
    id = request.POST.get("id")
    name = request.POST.get("name") 
    code = NoinfoProduct.objects.get(name=name).id
    times = request.POST.get("times") 
    date_watch_start = request.POST.get("date_watch_start")
    if date_watch_start == "":
        date_watch_start = None
    date_watch_end = request.POST.get("date_watch_end")
    if date_watch_end == "":
        date_watch_end = None
    status = request.POST.get("status")
    update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    try:
        NoinfoWatchrecord.objects.filter(id=id).update(
            name=name,
            code=code,
            times=times,
            date_watch_start=date_watch_start,
            date_watch_end=date_watch_end,
            status=status,
            update_time=update_time
        ) 
        code = 'success'
    except Exception as e:
        code = str(e)
        print(code)
    status = {
        'code': code
    }
    return JsonResponse(status, safe=False)

# 查询记录
def search_watch(request):
    return render(request, 'search_watch.html')
def search_watch_get(request):
    watchs = NoinfoWatchrecord.objects # 全部数据
    type1 = list(watchs.filter(times=1).values('type').annotate(count=Count('id'))) # 类型聚合计数
    all_num = watchs.filter(times=1).count() # 全部计数
    status_num = list(watchs.filter(date_watch_end__isnull=True).values('type').annotate(
        time1=Count(Case(When(times='1', then=Value(1)), output_field=IntegerField())),
        time2=Count(Case(When(times='2', then=Value(1)), output_field=IntegerField())),
    ).order_by('time1'))
    watchs = list(watchs.order_by('-date_watch_start').values()) # 格式化全部数据
    updating_s = NoinfoUpdating.objects.filter(status='更新').order_by('week')
    max_s = updating_s.aggregate(max_time=Max('update_time'))['max_time'].date()
    update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    current_date = datetime.now().date()
    if max_s < current_date:
        for us in updating_s:
            if us.update_time is None:
                s_date = us.create_time.date()
            else:
                s_date = us.update_time.date()
            w_count = 0
            s_date += timedelta(days=1)
            while s_date <= current_date:
                if s_date.weekday() == int(us.week) - 1:
                    w_count += 1
                s_date += timedelta(days=1)
            if w_count > 0:
                episodth = us.episodth + w_count
                NoinfoUpdating.objects.filter(title=us.title).update(
                        episodth = episodth,
                        update_time = update_time
                    )
    updating_sql = "SELECT id,week, GROUP_CONCAT(title||'('||COALESCE(episodth,'')||')') as titles FROM noinfo_updating where status='更新' GROUP BY week;"
    updatings = NoinfoUpdating.objects.raw(updating_sql)
    updating = []
    for u in updatings:
        updating.append({'week': u.week, 'titles': u.titles})
    updating_source = list(NoinfoUpdating.objects.filter(status='更新').order_by('week').values('title', 'week', 'status', 'episodth'))
    data = {
        'all_num': all_num,
        'watchs': watchs,
        'type1': type1,
        'status_num': status_num,
        'updating': updating,
        'updating_source': updating_source
    }
    return JsonResponse(data, safe=False)
@csrf_exempt
def search_watch_post(request):
    if request.method == 'POST':
        name_val = request.POST.get('name_val')
        status_id = request.POST.get('status_id')
        type_id = request.POST.get('type_id')
        if(status_id is None):
            type_list = NoinfoWatchrecord.objects.filter(name__icontains=name_val, type=type_id).order_by('-date_watch_start').values()
        elif(status_id == 'ing'):
            if(type_id == ''):
                type_list = NoinfoWatchrecord.objects.exclude(status='弃番').filter(name__icontains=name_val, date_watch_end__isnull=True).order_by('-date_watch_start').values()
            else:
                type_list = NoinfoWatchrecord.objects.exclude(status='弃番').filter(name__icontains=name_val, date_watch_end__isnull=True, type=type_id).order_by('-date_watch_start').values()
        elif(status_id == 'all'):
            if(type_id == ''):
                type_list = NoinfoWatchrecord.objects.filter(name__icontains=name_val).order_by('-date_watch_start').values()
            else:
                type_list = NoinfoWatchrecord.objects.filter(name__icontains=name_val, type=type_id).order_by('-date_watch_start').values()

        type_list = list(type_list)
        return JsonResponse(type_list, safe=False)
@csrf_exempt
def search_updating_post(request):
    if request.method == 'POST':
        week = request.POST.get('week')
        title = request.POST.get('title')
        episodth = request.POST.get('episodth')
        status = request.POST.get('status')
        weekdays = {"周一": 1, "周二": 2, "周三": 3, "周四": 4, "周五": 5, "周六": 6, "周日": 7}
        week = weekdays[week]
        weeks = NoinfoUpdating.objects.filter(title=title)
        if weeks:
            update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            NoinfoUpdating.objects.filter(title=title).update(
                week=week,
                status=status,
                episodth=episodth,
                update_time=update_time
            ) 
        else:
            create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            NoinfoUpdating.objects.create(
                week=week,
                title=title,
                status=status,
                episodth=episodth,
                create_time=create_time
            )
        updating_sql = "SELECT id,week, GROUP_CONCAT(title||'('||COALESCE(episodth,'')||')') as titles FROM noinfo_updating where status='更新' GROUP BY week;"
        updatings = NoinfoUpdating.objects.raw(updating_sql)
        updating = []
        for u in updatings:
            updating.append({'week': u.week, 'titles': u.titles})
        updating_source = list(NoinfoUpdating.objects.filter(status='更新').order_by('week').values('title', 'week', 'status', 'episodth'))
        data = {
            'updating': updating,
            'updating_source': updating_source  
        }
        return JsonResponse(data, safe=False)